Skip Headers

Oracle® Spatial User's Guide and Reference 10g Release 2 (10.2) Part Number B14255-03 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 11 Spatial Operators

This chapter describes the operators that you can use when working with the spatial object data type. For an overview of spatial operators, including how they differ from spatial procedures and functions, see Section 1.9. Table 11-1 lists the main operators.

Table 11-1 Main Spatial Operators

Operator | Description
--- | ---
SDO_FILTER | Specifies which geometries may interact with a given geometry.
SDO_JOIN | Performs a spatial join based on one or more topological relationships.
SDO_NN | Determines the nearest neighbor geometries to a geometry.
SDO_NN_DISTANCE | Returns the distance of an object returned by the SDO_NN operator.
SDO_RELATE | Determines whether or not two geometries interact in a specified way. (See also Table 11-2 for convenient alternative operators for performing specific mask value operations.)
SDO_WITHIN_DISTANCE | Determines if two geometries are within a specified distance from one another.

Table 11-2 lists operators, provided for convenience, that perform an SDO_RELATE operation of a specific mask type.

Table 11-2 Convenience Operators for SDO_RELATE Operations

Operator | Description
--- | ---
SDO_ANYINTERACT | Checks if any geometries in a table have the ANYINTERACT topological relationship with a specified geometry.
SDO_CONTAINS | Checks if any geometries in a table have the CONTAINS topological relationship with a specified geometry.
SDO_COVEREDBY | Checks if any geometries in a table have the COVEREDBY topological relationship with a specified geometry.
SDO_COVERS | Checks if any geometries in a table have the COVERS topological relationship with a specified geometry.
SDO_EQUAL | Checks if any geometries in a table have the EQUAL topological relationship with a specified geometry.
SDO_INSIDE | Checks if any geometries in a table have the INSIDE topological relationship with a specified geometry.
SDO_ON | Checks if any geometries in a table have the ON topological relationship with a specified geometry.
SDO_OVERLAPBDYDISJOINT | Checks if any geometries in a table have the OVERLAPBDYDISJOINT topological relationship with a specified geometry.
SDO_OVERLAPBDYINTERSECT | Checks if any geometries in a table have the OVERLAPBDYINTERSECT topological relationship with a specified geometry.
SDO_OVERLAPS | Checks if any geometries in a table overlap (that is, have the OVERLAPBDYDISJOINT or OVERLAPBDYINTERSECT topological relationship with) a specified geometry.
SDO_TOUCH | Checks if any geometries in a table have the TOUCH topological relationship with a specified geometry.

The rest of this chapter provides reference information on the operators, listed in alphabetical order.

For information about using operators with topologies, see Oracle Spatial Topology and Network Data Models.

## SDO_ANYINTERACT

Format

SDO_ANYINTERACT(geometry1, geometry2);

Description

Checks if any geometries in a table have the ANYINTERACT topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=ANYINTERACT'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_ANYINTERACT(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the ANYINTERACT topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the ANYINTERACT relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.)

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_ANYINTERACT(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(4,6, 8,8))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             1 cola_a
             4 cola_d

## SDO_CONTAINS

Format

SDO_CONTAINS(geometry1, geometry2);

Description

Checks if any geometries in a table have the CONTAINS topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=CONTAINS'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_CONTAINS(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the CONTAINS topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the CONTAINS relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 2,2, 4,6). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) In this example, only `cola_a` contains the query window geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_CONTAINS(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(2,2, 4,6))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             1 cola_a

## SDO_COVEREDBY

Format

SDO_COVEREDBY(geometry1, geometry2);

Description

Checks if any geometries in a table have the COVEREDBY topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=COVEREDBY'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_COVEREDBY(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the COVEREDBY topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the COVEREDBY relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 1,1, 5,8). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) In this example, only `cola_a` is covered by the query window geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_COVEREDBY(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(1,1, 5,8))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             1 cola_a

## SDO_COVERS

Format

SDO_COVERS(geometry1, geometry2);

Description

Checks if any geometries in a table have the COVERS topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=COVERS'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_COVERS(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the COVERS topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the COVERS relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 1,1, 4,6). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) In this example, only `cola_a` covers the query window geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_COVERS(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(1,1, 4,6))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             1 cola_a

## SDO_EQUAL

Format

SDO_EQUAL(geometry1, geometry2);

Description

Checks if any geometries in a table have the EQUAL topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=EQUAL'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_EQUAL(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the EQUAL topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the EQUAL relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 1,1, 5,7). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) In this example, `cola_a` (and only `cola_a`) has the same boundary and interior as the query window geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_EQUAL(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(1,1, 5,7))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             1 cola_a

## SDO_FILTER

Format

SDO_FILTER(geometry1, geometry2, param);

Description

Uses the spatial index to identify either the set of spatial objects that are likely to interact spatially with a given object (such as an area of interest), or pairs of spatial objects that are likely to interact spatially. Objects interact spatially if they are not disjoint.

This operator performs only a primary filter operation. The secondary filtering operation, performed by the SDO_RELATE operator, can be used to determine with certainty if objects interact spatially.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.
param | Optionally specifies either or both of the `min_resolution` and `max_resolution` keywords. Data type is VARCHAR2. The `min_resolution` keyword includes only geometries for which at least one side of the geometry's MBR is equal to or greater than the specified value. For example, `min_resolution=10` includes only geometries for which the width or the height (or both) of the geometry's MBR is at least 10. (This keyword can be used to exclude geometries that are too small to be of interest.) The `max_resolution` keyword includes only geometries for which at least one side of the geometry's MBR is less than or equal to the specified value. For example, `max_resolution=10` includes only geometries for which the width or the height (or both) of the geometry's MBR is less than or equal to 10. (This keyword can be used to exclude geometries that are too large to be of interest.)

Returns

The expression SDO_FILTER(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that are non-disjoint, and FALSE otherwise.

Usage Notes

SDO_FILTER is the only operator that can be used with data that is indexed using more than two dimensions. The operator considers all dimensions specified in the spatial index.

The operator must always be used in a WHERE clause and the condition that includes the operator should be an expression of the form SDO_FILTER(arg1, arg2) = 'TRUE'.

`geometry2` can come from a table or be a transient SDO_GEOMETRY object (such as a bind variable or SDO_GEOMETRY constructor).

- If the `geometry2` column is not spatially indexed, the operator indexes the query window in memory and performance is very good.

- If two or more geometries from `geometry2` are passed to the operator, the ORDERED optimizer hint must be specified, and the table in `geometry2` must be specified first in the FROM clause.

If `geometry1` and `geometry2` are based on different coordinate systems, `geometry2` is temporarily transformed to the coordinate system of `geometry1` for the operation to be performed, as described in Section 6.9.1.

RLS Restriction: If the DBMS_RLS.ADD_POLICY procedure has been used to add a fine-grained access control policy to a table or view, and if the specified policy function uses a spatial operator, the operator must be SDO_FILTER. No other spatial operators are supported in that context.

In previous releases, the SDO_FILTER operator required a third parameter. Effective with Oracle Spatial release 10.1, the operator has only two parameters. For backward compatibility, any keywords for the third parameter that were supported in the previous release will still work; however, the use of those keywords is discouraged and is not supported for new uses of the operator.

Examples

The following example selects the geometries that are likely to interact with a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.)

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_FILTER(c.shape,
        SDO_GEOMETRY(2003, NULL, NULL,
          SDO_ELEM_INFO_ARRAY(1,1003,3),
          SDO_ORDINATE_ARRAY(4,6, 8,8))
        ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             1 cola_a
             4 cola_d

The following example is the same as the preceding example, except that it includes only geometries where at least one side of the geometry's MBR is equal to or greater than 4.1. In this case, only `cola_a` and `cola_b` are returned, because their MBRs have at least one side with a length greater than or equal to 4.1. The circle `cola_d` is excluded, because its MBR is a square whose sides have a length of 4.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_FILTER(c.shape,
        SDO_GEOMETRY(2003, NULL, NULL,
          SDO_ELEM_INFO_ARRAY(1,1003,3),
          SDO_ORDINATE_ARRAY(4,6, 8,8)),
        'min_resolution=4.1'
        ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             1 cola_a

The following example selects the GID values from the POLYGONS table where the GEOMETRY column objects are likely to interact spatially with the GEOMETRY column object in the QUERY_POLYS table that has a GID value of 1.

    SELECT A.gid
      FROM Polygons A, query_polys B
      WHERE B.gid = 1
      AND SDO_FILTER(A.Geometry, B.Geometry) = 'TRUE';

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with the geometry stored in the `aGeom` variable.

    Select A.Gid
      FROM Polygons A
      WHERE SDO_FILTER(A.Geometry, :aGeom) = 'TRUE';

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).

    Select A.Gid
      FROM Polygons A
      WHERE SDO_FILTER(A.Geometry, sdo_geometry(2003,NULL,NULL,
                                       sdo_elem_info_array(1,1003,3),
                                       sdo_ordinate_array(x1,y1,x2,y2))
                       ) = 'TRUE';

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with any GEOMETRY column object in the QUERY_POLYS table. In this example, the ORDERED optimizer hint is used and the QUERY_POLYS (`geometry2`) table is specified first in the FROM clause, because multiple geometries from `geometry2` are involved (see the Usage Notes).

    SELECT /*+ ORDERED */
      A.gid
      FROM query_polys B, polygons A
      WHERE SDO_FILTER(A.Geometry, B.Geometry) = 'TRUE';

Related Topics

- SDO_RELATE

## SDO_INSIDE

Format

SDO_INSIDE(geometry1, geometry2);

Description

Checks if any geometries in a table have the INSIDE topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=INSIDE'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_INSIDE(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the INSIDE topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the INSIDE relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 5,6, 12,12). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) In this example, only `cola_d` (the circle) is inside the query window geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_INSIDE(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(5,6, 12,12))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             4 cola_d

## SDO_JOIN

Format

SDO_JOIN(table_name1, column_name1, table_name2, column_name2, params, preserve_join_order) RETURN SDO_ROWIDSET;

Description

Performs a spatial join based on one or more topological relationships.

Keywords and Parameters

Value | Description
--- | ---
table_name1 | Name of the first table to be used in the spatial join operation. The table must have a column of type SDO_GEOMETRY. Data type is VARCHAR2.
column_name1 | Name of the spatial column of type SDO_GEOMETRY in `table_name1`. A spatial R-tree index must be defined on this column. Data type is VARCHAR2.
table_name2 | Name of the second table to be used in the spatial join operation. (It can be the same as or different from `table_name1`.) The table must have a column of type SDO_GEOMETRY. Data type is VARCHAR2.
column_name2 | Name of the spatial column of type SDO_GEOMETRY in `table_name2`. A spatial R-tree index must be defined on this column. Data type is VARCHAR2.
params | Optional parameter string of keywords and values; available only if `mask=ANYINTERACT`. Determines the behavior of the operator. See Table 11-3 in the Usage Notes for information about the available keywords. Data type is VARCHAR2. Default is NULL.
preserve_join_order | Optional parameter to specify if the join order is guaranteed to be preserved during processing of the operator. If the value is `0` (the default), the order of the tables might be changed; if the value is `1`, the order of the tables is not changed. Data type is NUMBER. Default is 0.

Returns

SDO_JOIN returns an object of SDO_ROWIDSET, which consists of a table of objects of SDO_ROWIDPAIR. Oracle Spatial defines the type SDO_ROWIDSET as:

    CREATE TYPE sdo_rowidset as TABLE OF sdo_rowidpair;

Oracle Spatial defines the object type SDO_ROWIDPAIR as:

    CREATE TYPE sdo_rowidpair AS OBJECT
       (rowid1  VARCHAR2(24),
        rowid2  VARCHAR2(24));

In the SDO_ROWIDPAIR definition, `rowid1` refers to a rowid from `table_name1`, and `rowid2` refers to a rowid from `table_name2`.

Usage Notes

SDO_JOIN is technically not an operator, but a table function. (For an explanation of table functions, see PL/SQL User's Guide and Reference.) However, it is presented in the chapter with Spatial operators because its usage is similar to that of the operators, and because it is not part of a package with other functions and procedures.

This table function is recommended when you need to perform full table joins.

The geometries in `column_name1` and `column_name2` must have the same SRID (coordinate system) value and the same number of dimensions.

For best performance, use the `/*+ ORDERED */` optimizer hint, and specify the SDO_JOIN table function first in the FROM clause.

If a table is version-enabled (using the Workspace Manager feature), you must specify the `<table_name>``_LT` table created by Workspace Manager. For example, if the COLA_MARKETS table is version-enabled and you want to perform a spatial join operation on that table, specify COLA_MARKETS_LT (not COLA_MARKETS) with the SDO_JOIN table function. (However, for all other Spatial functions, procedures, and operators, do not use the `<table_name>``_LT` name.)

The SDO_JOIN table function is not supported on partitioned tables.

Table 11-3 shows the keywords for the `params` parameter.

Table 11-3 params Keywords for the SDO_JOIN Operator

Keyword | Description
--- | ---
mask | The topological relationship of interest.Valid values are 'mask=<value>' where <value> is one or more of the mask values valid for the SDO_RELATE operator (`TOUCH`, `OVERLAPBDYDISJOINT`, `OVERLAPBDYINTERSECT`, `EQUAL`, `INSIDE`, `COVEREDBY`, `CONTAINS`, `COVERS`, `ANYINTERACT`, `ON`), or `FILTER`, which checks if the MBRs (the filter-level approximations) intersect. Multiple masks are combined with the logical Boolean operator OR (for example, `'mask=inside+touch'`); however, `FILTER` cannot be combined with any other mask. If this parameter is null or contains an empty string, `mask=FILTER` is assumed.
distance | Specifies a numeric distance value that is added to the tolerance value (explained in Section 1.5.5) before the relationship checks are performed. For example, if the tolerance is 10 meters and you specify `'distance=100 unit=meter'`, two objects are considered to have spatial interaction if they are within 110 meters of each other.If you specify `distance` but not `unit`, the unit of measurement associated with the data is assumed.
unit | Specifies a unit of measurement to be associated with the distance value (for example, `'distance=100 unit=meter'`). See Section 2.8 for more information about unit of measurement specification. If you specify `unit`, you must also specify `distance`.Data type is VARCHAR2. Default = unit of measurement associated with the data. For geodetic data, the default is meters.

Examples

The following example joins the COLA_MARKETS table with itself to find, for each geometry, all other geometries that have any spatial interaction with it. (The example uses the definitions and data from Section 2.1.) In this example, `rowid1` and `rowid2` correspond to the names of the attributes in the SDO_ROWIDPAIR type definition. Note that in the output, `cola_d` (the circle in Figure 2-1) interacts only with itself, and not with any of the other geometries.

    SELECT /*+ ordered */ a.name, b.name
      FROM TABLE(SDO_JOIN('COLA_MARKETS', 'SHAPE',
                          'COLA_MARKETS', 'SHAPE',
                          'mask=ANYINTERACT')) c,
           cola_markets a,
           cola_markets b
      WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid
      ORDER BY a.name;

    NAME                             NAME
    -------------------------------- --------------------------------
    cola_a                           cola_c
    cola_a                           cola_b
    cola_a                           cola_a
    cola_b                           cola_c
    cola_b                           cola_b
    cola_b                           cola_a
    cola_c                           cola_c
    cola_c                           cola_b
    cola_c                           cola_a
    cola_d                           cola_d

    10 rows selected.

Related Topics

- SDO_RELATE

## SDO_NN

Format

SDO_NN(geometry1, geometry2, param [, number]);

Description

Uses the spatial index to identify the nearest neighbors for a geometry.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. The nearest neighbor or neighbors to `geometry2` will be returned from `geometry1`. (`geometry2` is specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.
param | Determines the behavior of the operator. The available keywords are listed in Table 11-4. If you do not specify this parameter, the operator returns all rows in increasing distance order from `geometry2`. Data type is VARCHAR2.
number | If the SDO_NN_DISTANCE ancillary operator is included in the call to SDO_NN, specifies the same number used in the call to SDO_NN_DISTANCE. Data type is NUMBER.

Table 11-4 lists the keywords for the `param` parameter.

Table 11-4 Keywords for the SDO_NN Param Parameter

Keyword | Description
--- | ---
sdo_batch_size | Specifies the number of rows to be evaluated at a time when the SDO_NN expression may need to be evaluated multiple times in order to return the desired number of results that satisfy the WHERE clause. Available only when an R-tree index is used. If you specify `sdo_batch_size=0` (or if you omit the `param` parameter completely), Spatial calculates a batch size suited to the result set size. See the Usage Notes and Examples for more information. Data type is NUMBER. For example: 'sdo_batch_size=10'
sdo_num_res | If `sdo_batch_size` is not specified, specifies the number of results (nearest neighbors) to be returned. If `sdo_batch_size` is specified, this keyword is ignored; instead, use the ROWNUM pseudocolumn to limit the number of results. See the Usage Notes and Examples for more information. Data type is NUMBER. For example: 'sdo_num_res=5'
unit | If the SDO_NN_DISTANCE ancillary operator is included in the call to SDO_NN, specifies the unit of measurement: a quoted string with `unit=` and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table. See Section 2.8 for more information about unit of measurement specification. Data type is VARCHAR2. Default = unit of measurement associated with the data. For geodetic data, the default is meters. For example: 'unit=KM'

Returns

This operator returns the `sdo_num_res` number of objects from `geometry1` that are nearest to `geometry2` in the query. In determining how near two geometry objects are, the shortest possible distance between any two points on the surface of each object is used.

Usage Notes

The operator is disabled if the table does not have a spatial index or if the index has been built on more than two dimensions.

The operator must always be used in a WHERE clause, and the condition that includes the operator should be an expression of the form SDO_NN(arg1, arg2, '<some_parameter>') = 'TRUE'.

The operator can be used in two ways:

- If all geometries in the layer are candidates, use the `sdo_num_res` keyword to specify the number of geometries returned.

- If any geometries in the table might be nearer than the geometries specified in the WHERE clause, use the `sdo_batch_size` keyword and use the WHERE clause (including the ROWNUM pseudocolumn) to limit the number of geometries returned.

As an example of the `sdo_batch_size` keyword, assume that a RESTAURANTS table contains different types of restaurants, and you want to find the two nearest Italian restaurants to your hotel. The query might look like the following:

    SELECT r.name FROM restaurants r WHERE
       SDO_NN(r.geometry, :my_hotel, 'sdo_batch_size=10') = 'TRUE'
       AND r.cuisine = 'Italian' AND ROWNUM <=2;

In this example, the `ROWNUM <=2` clause is necessary to limit the number of results returned to no more than 2 where CUISINE is `Italian`. However, if the `sdo_batch_size` keyword is not specified in this example, and if `sdo_num_res=2` is specified instead of `ROWNUM <=2`, only the two nearest restaurants are considered, regardless of their CUISINE value; and if the CUISINE value of these two rows is not `Italian`, the query may return no rows.

The `sdo_batch_size` value can affect the performance of nearest neighbor queries. A good general guideline is to specify the number of candidate rows likely to satisfy the WHERE clause. Using the preceding example of a query for Italian restaurants, if approximately 20 percent of the restaurants nearest to the hotel are Italian and if you want 2 restaurants, an `sdo_batch_size` value of 10 will probably result in the best performance. On the other hand, if only approximately 5 percent of the restaurants nearest to the hotel are Italian and if you want 2 restaurants, an `sdo_batch_size` value of 40 would be better.

You can specify `sdo_batch_size=0`, which causes Spatial to calculate a batch size that is suitable for the result set size. However, the calculated batch size may not be optimal, and the calculation incurs some processing overhead; if you can determine a good `sdo_batch_size` value for a query, the performance will probably be better than if you specify `sdo_batch_size=0`.

If the `sdo_batch_size` keyword is specified, any `sdo_num_res` value is ignored. Do not specify both keywords.

Specify the `number` parameter only if you are using the SDO_NN_DISTANCE ancillary operator in the call to SDO_NN. See the information about the SDO_NN_DISTANCE operator in this chapter.

If this operator is used with geodetic data, the data must be indexed with an R-tree spatial index. If this operator is used with geodetic data and if the R-tree spatial index is created with `'geodetic=false'` specified, you cannot use the `unit` parameter.

If two or more objects from `geometry1` are an equal distance from `geometry2`, any of the objects can be returned on any call to the function. For example, if `item_a`, `item_b`, and `item_c` are nearest to and equally distant from `geometry2,` and if `sdo_num_res=2`, two of those three objects are returned, but they can be any two of the three.

If the SDO_NN operator uses a partitioned spatial index (see Section 4.1.4), the requested number of geometries is returned for each partition that contains candidate rows based on the query criteria. For example, if you request the 5 nearest restaurants to a point and the spatial index has 4 partitions, the operator returns up to 20 (5*4) geometries. In this case, you must use the ROWNUM pseudocolumn (here, `WHERE ROWNUM <=5`) to return the 5 nearest restaurants.

If `geometry1` and `geometry2` are based on different coordinate systems, `geometry2` is temporarily transformed to the coordinate system of `geometry1` for the operation to be performed, as described in Section 6.9.1.

SDO_NN is not supported for spatial joins.

In some situations the SDO_NN operator will not use the spatial index unless an optimizer hint forces the index to be used. This can occur when a query involves a join; and if the optimizer hint is not used in such situations, an internal error occurs. To prevent such errors, you should always specify an optimizer hint to use the spatial index with the SDO_NN operator, regardless of how simple or complex the query is. For example, the following excerpt from a query specifies to use the COLA_SPATIAL_IDX index that is defined on the COLA_MARKETS table:

    SELECT /*+ INDEX(c cola_spatial_idx) */
      c.mkt_id, c.name, ... FROM cola_markets c, ...;

However, if the column predicate in the WHERE clause specifies any nonspatial column in the table for `geometry1` that has an associated index, be sure that this index is not used by specifying the NO_INDEX hint for that index. For example, if there was an index named COLA_NAME_IDX defined on the NAME column, you would need to specify the hints in the preceding example as follows:

    SELECT /*+ INDEX(c cola_spatial_idx) NO_INDEX(c cola_name_idx) */
      c.mkt_id, c.name, ... FROM cola_markets c, ...;

(Note, however, that there is no index named COLA_NAME_IDX in the example in Section 2.1.)

If you join two or more tables with the SDO_NN operator, specify the LEADING hint for the outer table and the INDEX hint for the inner table (the table with the spatial index), and specify the inner table last. For example:

    SELECT  /*+ LEADING(b)  INDEX(a cola_spatial_idx) */ a.gid
       FROM cola_qry  b,  cola_markets  a
          WHERE b.gid =1 AND
              SDO_NN(a.shape, b.shape, 'querytype=window sdo_num_res=1')='TRUE';

For detailed information about using optimizer hints, see Oracle Database Performance Tuning Guide.

Examples

The following example finds the two objects from the SHAPE column in the COLA_MARKETS table that are nearest to a specified point (10,7). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.)

    SELECT /*+ INDEX(c cola_spatial_idx) */
     c.mkt_id, c.name  FROM cola_markets c  WHERE SDO_NN(c.shape,
       sdo_geometry(2001, NULL, sdo_point_type(10,7,NULL), NULL,
       NULL),  'sdo_num_res=2') = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             4 cola_d

The following example uses the `sdo_batch_size` keyword to find the two objects (ROWNUM <=2), with a NAME value less than 'cola_d', from the SHAPE column in the COLA_MARKETS table that are nearest to a specified point (10,7). The value of 3 for `sdo_batch_size` represents a best guess at the number of nearest geometries that need to be evaluated before the WHERE clause condition is satisfied. (The example uses the definitions and data from Section 2.1.)

    SELECT /*+ INDEX(c cola_spatial_idx) */ c.mkt_id, c.name
       FROM cola_markets c
       WHERE SDO_NN(c.shape,  sdo_geometry(2001, NULL,
          sdo_point_type(10,7,NULL), NULL,  NULL),
          'sdo_batch_size=3') = 'TRUE'
       AND c.name < 'cola_d' AND ROWNUM <= 2;

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             3 cola_c

See also the more complex SDO_NN examples in Section D.3.

Related Topics

- SDO_NN_DISTANCE

## SDO_NN_DISTANCE

Format

SDO_NN_DISTANCE(number);

Description

Returns the distance of an object returned by the SDO_NN operator. Valid only within a call to the SDO_NN operator.

Keywords and Parameters

Value | Description
--- | ---
number | Specifies a number that must be the same as the last parameter passed to the SDO_NN operator. Data type is NUMBER.

Returns

This operator returns the distance of an object returned by the SDO_NN operator. In determining how near two geometry objects are, the shortest possible distance between any two points on the surface of each object is used.

Usage Notes

SDO_NN_DISTANCE is an ancillary operator to the SDO_NN operator. It returns the distance between the specified geometry and a nearest neighbor object. This distance is passed as ancillary data to the SDO_NN operator. (For an explanation of how operators can use ancillary data, see the section on ancillary data in the chapter on domain indexes in Oracle Data Cartridge Developer's Guide.)

You can choose any arbitrary number for the `number` parameter. The only requirement is that it must match the last parameter in the call to the SDO_NN operator.

Use a bind variable to store and operate on the distance value.

Examples

The following example finds the two objects from the SHAPE column in the COLA_MARKETS table that are nearest to a specified point (10,7), and it finds the distance between each object and the point. (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.)

    SELECT   /*+ INDEX(c cola_spatial_idx) */
       c.mkt_id, c.name, SDO_NN_DISTANCE(1) dist
       FROM cola_markets c
       WHERE SDO_NN(c.shape,  sdo_geometry(2001, NULL,
          sdo_point_type(10,7,NULL), NULL,  NULL),
          'sdo_num_res=2', 1) = 'TRUE' ORDER BY dist;

        MKT_ID NAME                                   DIST
    ---------- -------------------------------- ----------
             4 cola_d                           .828427125
             2 cola_b                           2.23606798

Note the following about this example:

- 1 is used as the `number` parameter for SDO_NN_DISTANCE, and 1 is also specified as the last parameter to SDO_NN (after `'sdo_num_res=2'`).

- The column alias `dist` holds the distance between the object and the point. (For geodetic data, the distance unit is meters; for non-geodetic data, the distance unit is the unit associated with the data.)

Related Topics

- SDO_NN

## SDO_ON

Format

SDO_ON(geometry1, geometry2);

Description

Checks if any geometries in a table have the ON topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=ON'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_ON(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the ON topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the ON relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) This example returns no rows because there are no line string geometries in the SHAPE column.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_ON(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(4,6, 8,8))
                ) = 'TRUE';

    no rows selected

## SDO_OVERLAPBDYDISJOINT

Format

SDO_OVERLAPBDYDISJOINT(geometry1, geometry2);

Description

Checks if any geometries in a table have the OVERLAPBDYDISJOINT topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=OVERLAPBDYDISJOINT'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_OVERLAPBDYDISJOINT(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the OVERLAPBDYDISJOINT topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the OVERLAPBDYDISJOINT relationship with a line string geometry (here, a horizontal line from 0,6 to 2,6). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) In this example, only `cola_a` has the OVERLAPBDYDISJOINT relationship with the line string geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_OVERLAPBDYDISJOINT(c.shape,
                SDO_GEOMETRY(2002, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,2,1),
                  SDO_ORDINATE_ARRAY(0,6, 2,6))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             1 cola_a

## SDO_OVERLAPBDYINTERSECT

Format

SDO_OVERLAPBDYINTERSECT(geometry1, geometry2);

Description

Checks if any geometries in a table have the OVERLAPBDYINTERSECT topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=OVERLAPBDYINTERSECT'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_OVERLAPBDYINTERSECT(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the OVERLAPBDYINTERSECT topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the OVERLAPBDYINTERSECT relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) In this example, `cola_a`, `cola_b`, and `cola_d` have the OVERLAPBDYINTERSECT relationship with the query window geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_OVERLAPBDYINTERSECT(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(4,6, 8,8))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             1 cola_a
             4 cola_d

## SDO_OVERLAPS

Format

SDO_OVERLAPS(geometry1, geometry2);

Description

Checks if any geometries in a table overlap (that is, have the OVERLAPBDYDISJOINT or OVERLAPBDYINTERSECT topological relationship with) a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_OVERLAPS(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the OVERLAPBDYDISJOINT or OVERLAPBDYINTERSECT topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that overlap a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.) In this example, three of the geometries in the SHAPE column overlap the query window geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_OVERLAPS(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(4,6, 8,8))
                ) = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             1 cola_a
             4 cola_d

## SDO_RELATE

Format

SDO_RELATE(geometry1, geometry2, param);

Description

Uses the spatial index to identify either the spatial objects that have a particular spatial interaction with a given object such as an area of interest, or pairs of spatial objects that have a particular spatial interaction.

This operator performs both primary and secondary filter operations.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.
param | Specifies the `mask` keyword, and optionally either or both of the `min_resolution` and `max_resolution` keywords. The data type for this parameter is VARCHAR2. The `mask` keyword specifies the topological relationship of interest. This is a required parameter. Valid `mask` keyword values are one or more of the following in the nine-intersection pattern: `TOUCH`, `OVERLAPBDYDISJOINT`, `OVERLAPBDYINTERSECT`, `EQUAL`, `INSIDE`, `COVEREDBY`, `CONTAINS`, `COVERS`, `ANYINTERACT`, `ON`. Multiple masks are combined with the logical Boolean operator OR, for example, '`mask=inside+touch'`; however, see the Usage Notes for an alternative syntax using UNION ALL that may result in better performance. See Section 1.8 for an explanation of the nine-intersection relationship pattern. The `min_resolution` keyword includes only geometries for which at least one side of the geometry's MBR is equal to or greater than the specified value. For example, `min_resolution=10` includes only geometries for which the width or the height (or both) of the geometry's MBR is at least 10. (This keyword can be used to exclude geometries that are too small to be of interest.) The `max_resolution` keyword includes only geometries for which at least one side of the geometry's MBR is less than or equal to the specified value. For example, `max_resolution=10` includes only geometries for which the width or the height (or both) of the geometry's MBR is less than or equal to 10. (This keyword can be used to exclude geometries that are too large to be of interest.) For backward compatibility, any additional keywords for the `param` parameter that were supported before release 10.1 will still work; however, the use of those keywords is discouraged and is not supported for new uses of the operator.

Returns

The expression SDO_RELATE(geometry1,geometry2, 'mask = <some_mask_val>') = 'TRUE' evaluates to TRUE for object pairs that have the topological relationship specified by <some_mask_val>, and FALSE otherwise.

Usage Notes

The operator is disabled if the table does not have a spatial index or if the index has been built on more than two dimensions.

The operator must always be used in a WHERE clause, and the condition that includes the operator should be an expression of the form SDO_RELATE(arg1, arg2, 'mask = <some_mask_val>') = 'TRUE'.

`geometry2` can come from a table or be a transient SDO_GEOMETRY object (such as a bind variable or SDO_GEOMETRY constructor).

- If the `geometry2` column is not spatially indexed, the operator indexes the query window in memory and performance is very good.

- If two or more geometries from `geometry2` are passed to the operator, the ORDERED optimizer hint must be specified, and the table in `geometry2` must be specified first in the FROM clause.

If `geometry1` and `geometry2` are based on different coordinate systems, `geometry2` is temporarily transformed to the coordinate system of `geometry1` for the operation to be performed, as described in Section 6.9.1.

Unlike with the SDO_GEOM.RELATE function, DISJOINT and DETERMINE masks are not allowed in the relationship mask with the SDO_RELATE operator. This is because SDO_RELATE uses the spatial index to find candidates that may interact, and the information to satisfy DISJOINT or DETERMINE is not present in the index.

Although multiple masks can be combined using the logical Boolean operator OR, for example, `'mask=touch+coveredby'`, better performance may result if the spatial query specifies each mask individually and uses the UNION ALL syntax to combine the results. This is due to internal optimizations that Spatial can apply under certain conditions when masks are specified singly rather than grouped within the same SDO_RELATE operator call. (There are two exceptions, `inside+coveredby` and `contains+covers`, where the combination performs better than the UNION ALL alternative.) For example, consider the following query using the logical Boolean operator OR to group multiple masks:

    SELECT a.gid
      FROM polygons a, query_polys B
      WHERE B.gid = 1
      AND SDO_RELATE(A.Geometry, B.Geometry,
                       'mask=touch+coveredby') = 'TRUE';

The preceding query may result in better performance if it is expressed as follows, using UNION ALL to combine results of multiple SDO_RELATE operator calls, each with a single mask:

    SELECT a.gid
          FROM polygons a, query_polys B
          WHERE B.gid = 1
          AND SDO_RELATE(A.Geometry, B.Geometry,
                       'mask=touch') = 'TRUE'
    UNION ALL
    SELECT a.gid
          FROM polygons a, query_polys B
          WHERE B.gid = 1
          AND SDO_RELATE(A.Geometry, B.Geometry,
                       'mask=coveredby') = 'TRUE';

The following considerations apply to relationships between lines and a multipoint geometry (points in a point cluster). Assume the example of a line and a multipoint geometry (for example, SDO_GTYPE = 2005) consisting of three points.

- If none of the points has any interaction with the line, the relationship between the line and the point cluster is DISJOINT.

- If one of the points is on the interior of the line and the other two points are disjoint, the relationship between the line and the point cluster is OVERLAPBDYDISJOINT.

- If one of the points is on the boundary of the line (that is, if it is on the start point or end point of the line) and the other two points are disjoint, the relationship between the line and the point cluster is TOUCH.

- If one of the points is on the boundary of the line (that is, if it is on the start point or end point of the line), another point is on the interior of the line, and the third point is disjoint, the relationship between the line and the point cluster is OVERLAPBDYDISJOINT (not OVERLAPBDYINTERSECT).

Examples

The following examples are similar to those for the SDO_FILTER operator; however, they identify a specific type of interaction (using the `mask` keyword), and they determine with certainty (not mere likelihood) if the spatial interaction occurs.

The following example selects the geometries that have any interaction with a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1.)

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_RELATE(c.shape,
        SDO_GEOMETRY(2003, NULL, NULL,
          SDO_ELEM_INFO_ARRAY(1,1003,3),
          SDO_ORDINATE_ARRAY(4,6, 8,8)),
        'mask=anyinteract') = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             1 cola_a
             4 cola_d

The following example is the same as the preceding example, except that it includes only geometries where at least one side of the geometry's MBR is equal to or greater than 4.1. In this case, only `cola_a` and `cola_b` are returned, because their MBRs have at least one side with a length greater than or equal to 4.1. The circle `cola_d` is excluded, because its MBR is a square whose sides have a length of 4.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_RELATE(c.shape,
        SDO_GEOMETRY(2003, NULL, NULL,
          SDO_ELEM_INFO_ARRAY(1,1003,3),
          SDO_ORDINATE_ARRAY(4,6, 8,8)),
        'mask=anyinteract min_resolution=4.1') = 'TRUE';

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b
             1 cola_a

The following example selects the GID values from the POLYGONS table where the GEOMETRY column objects have any spatial interaction with the GEOMETRY column object in the QUERY_POLYS table that has a GID value of 1.

    SELECT A.gid
      FROM Polygons A, query_polys B
      WHERE B.gid = 1
      AND SDO_RELATE(A.Geometry, B.Geometry,
                       'mask=ANYINTERACT') = 'TRUE';

The following example selects the GID values from the POLYGONS table where a GEOMETRY column object has any spatial interaction with the geometry stored in the `aGeom` variable.

    SELECT A.Gid
      FROM Polygons A
      WHERE SDO_RELATE(A.Geometry, :aGeom, 'mask=ANYINTERACT') = 'TRUE';

The following example selects the GID values from the POLYGONS table where a GEOMETRY column object has any spatial interaction with the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).

    SELECT A.Gid
      FROM Polygons A
      WHERE SDO_RELATE(A.Geometry, sdo_geometry(2003,NULL,NULL,
                                       sdo_elem_info_array(1,1003,3),
                                       sdo_ordinate_array(x1,y1,x2,y2)),
                         'mask=ANYINTERACT') = 'TRUE';

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object has any spatial interaction with any GEOMETRY column object in the QUERY_POLYS table. In this example, the ORDERED optimizer hint is used and QUERY_POLYS (`geometry2`) table is specified first in the FROM clause, because multiple geometries from `geometry2` are involved (see the Usage Notes).

    SELECT /*+ ORDERED */
      A.gid
      FROM query_polys B, polygons A
      WHERE SDO_RELATE(A.Geometry, B.Geometry, 'mask=ANYINTERACT') = 'TRUE';

Related Topics

- SDO_FILTER

- SDO_JOIN

- SDO_WITHIN_DISTANCE

- SDO_GEOM.RELATE function

## SDO_TOUCH

Format

SDO_TOUCH(geometry1, geometry2);

Description

Checks if any geometries in a table have the TOUCH topological relationship with a specified geometry. Equivalent to specifying the SDO_RELATE operator with `'mask=TOUCH'`.

See the section on the SDO_RELATE operator in this chapter for information about the operations performed by this operator and for usage requirements.

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.
geometry2 | Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

Returns

The expression SDO_TOUCH(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the TOUCH topological relationship, and FALSE otherwise.

Usage Notes

See the Usage Notes for the SDO_RELATE operator in this chapter.

For an explanation of the topological relationships and the nine-intersection model used by Spatial, see Section 1.8.

Examples

The following example finds geometries that have the TOUCH relationship with a query window (here, a rectangle with lower-left, upper-right coordinates 1,1, 5,7). (The example uses the definitions and data in Section 2.1 and illustrated in Figure 2-1.) In this example, only `cola_b` has the TOUCH relationship with the query window geometry.

    SELECT c.mkt_id, c.name
      FROM cola_markets c
      WHERE SDO_TOUCH(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(1,1, 5,7))
                ) = 'TRUE';
      FROM cola_markets c

        MKT_ID NAME
    ---------- --------------------------------
             2 cola_b

## SDO_WITHIN_DISTANCE

Format

SDO_WITHIN_DISTANCE(geometry1, aGeom, params);

Description

Uses the spatial index to identify the set of spatial objects that are within some specified distance of a given object (such as an area of interest or point of interest).

Keywords and Parameters

Value | Description
--- | ---
geometry1 | Specifies a geometry column in a table. The column has the set of geometry objects that will be operated on to determine if they are within the specified distance of the given object (`aGeom`). The column must be spatially indexed. Data type is SDO_GEOMETRY.
aGeom | Specifies the object to be checked for distance against the geometry objects in `geometry1`. Specify either a geometry from a table (using a bind variable) or a transient instance of a geometry (using the SDO_GEOMETRY constructor). Data type is SDO_GEOMETRY.
params | A quoted string containing one or more keywords (with values) that determine the behavior of the operator. The remaining items (`distance`, `max_resolution`, `min_resolution`, `querytype`, and `unit`) are potential keywords for the `params` parameter. Data type is VARCHAR2.
distance | Specifies the distance value. If a coordinate system is associated with the geometry, the distance unit is assumed to be the unit associated with the coordinate system. This is a required keyword. Data type is NUMBER.
max_resolution | Includes only geometries for which at least one side of the geometry's MBR is less than or equal to the specified value. For example, `max_resolution=10` includes only geometries for which the width or the height (or both) of the geometry's MBR is less than or equal to 10. (This keyword can be used to exclude geometries that are too large to be of interest.)
min_resolution | Includes only geometries for which at least one side of the geometry's MBR is equal to or greater than the specified value. For example, `min_resolution=10` includes only geometries for which the width or the height (or both) of the geometry's MBR is at least 10. (This keyword can be used to exclude geometries that are too small to be of interest.)
querytype | Set `'querytype=FILTER'` to perform only a primary filter operation. If `querytype` is not specified, both primary and secondary filter operations are performed (default). Data type is VARCHAR2.
unit | Specifies the unit of measurement: a quoted string with `unit=` and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table (for example, `'unit=KM'`). See Section 2.8 for more information about unit of measurement specification. Data type is NUMBER. Default = unit of measurement associated with the data. For geodetic data, the default is meters.

Returns

The expression SDO_WITHIN_DISTANCE(arg1, arg2, arg3) = 'TRUE' evaluates to TRUE for object pairs that are within the specified distance, and FALSE otherwise.

Usage Notes

The distance between two extended objects (nonpoint objects such as lines and polygons) is defined as the minimum distance between these two objects. The distance between two adjacent polygons is zero.

If this operator is used with geodetic data, the data must be indexed with an R-tree spatial index. If this operator is used with geodetic data and if the R-tree spatial index is created with `'geodetic=false'` specified, you cannot use the `unit` parameter.

The operator is disabled if the table does not have a spatial index or if the index has been built on more than two dimensions.

The operator must always be used in a WHERE clause and the condition that includes the operator should be an expression of the form:

    SDO_WITHIN_DISTANCE(arg1, arg2, 'distance = <some_dist_val>') = 'TRUE'

The geometry column must have a spatial index built on it. If the data is geodetic, the spatial index must be an R-tree index.

SDO_WITHIN_DISTANCE is not supported for spatial joins. See Section 4.2.1.3 for a discussion on how to perform a spatial join within-distance operation.

Examples

The following example selects the geometries that are within a distance of 10 from a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described in Section 2.1 and illustrated in Figure 2-1. In this case, all geometries shown in that figure are returned.)

    SELECT c.name FROM cola_markets c WHERE SDO_WITHIN_DISTANCE(c.shape,
      SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
        SDO_ORDINATE_ARRAY(4,6, 8,8)),
      'distance=10') = 'TRUE';

    NAME
    --------------------------------
    cola_b
    cola_a
    cola_c
    cola_d

The following example is the same as the preceding example, except that it includes only geometries where at least one side of the geometry's MBR is equal to or greater than 4.1. In this case, only `cola_a` and `cola_b` are returned, because their MBRs have at least one side with a length greater than or equal to 4.1. The trapezoid `cola_c` is excluded, because its MBR has sides with lengths of 3 and 2; and the circle `cola_d` is excluded, because its MBR is a square whose sides have a length of 4.

    SELECT c.name FROM cola_markets c WHERE SDO_WITHIN_DISTANCE(c.shape,
      SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
        SDO_ORDINATE_ARRAY(4,6, 8,8)),
      'distance=10 min_resolution=4.1') = 'TRUE';

    NAME
    --------------------------------
    cola_b
    cola_a

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is within 10 distance units of the geometry stored in the `aGeom` variable.

    SELECT A.GID    FROM POLYGONS A    WHERE      SDO_WITHIN_DISTANCE(A.Geometry, :aGeom, 'distance = 10') = 'TRUE';

The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is within 10 distance units of the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).

    SELECT A.GID    FROM POLYGONS A    WHERE      SDO_WITHIN_DISTANCE(A.Geometry, sdo_geometry(2003,NULL,NULL,                              sdo_elem_info_array(1,1003,3),                              sdo_ordinate_array(x1,y1,x2,y2)),                      'distance = 10') = 'TRUE';

The following example selects the GID values from the POLYGONS table where the GID value in the QUERY_POINTS table is 1 and a POLYGONS.GEOMETRY object is within 10 distance units of the QUERY_POINTS.GEOMETRY object.

    SELECT A.GID    FROM POLYGONS A, Query_Points B    WHERE B.GID = 1 AND      SDO_WITHIN_DISTANCE(A.Geometry, B.Geometry, 'distance = 10') = 'TRUE';

See also the more complex SDO_WITHIN_DISTANCE examples in Section D.2.

Related Topics

- SDO_FILTER

- SDO_RELATE